package com.zkyc.framework.website.mapper;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zkyc.framework.common.pojo.MediaTag;
import com.zkyc.framework.common.pojo.WebContent;
import com.zkyc.framework.common.vo.website.WebContentPublishVo;
import com.zkyc.framework.common.vo.website.WebContentVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author bing.bai
 * @create 2022/5/25
 */
@Mapper
public interface ContentMapper extends BaseMapper<WebContent> {

    @Update({"<script>" +
            "update web_content set content=replace(content,#{sourceText}, #{targetText}), content_title=replace(content_title,#{sourceText}, #{targetText}) ,original_content=replace(original_content,#{sourceText}, #{targetText}), gmt_updated=CURRENT_TIMESTAMP" +
            " where id in" +
            "    <foreach item='item' index='index' collection='contentIds' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "    </foreach>" +
            "</script>"
    })
    void repalceContent(@Param("sourceText") String sourceText,
                        @Param("targetText") String targetText,
                        @Param("contentIds") List<Integer> contentIds);

    @Select({"<script>" +
            "SELECT a.* from web_content a,web_quote_content b" +
            " where a.id=b.content_id " +
            " and a.is_deleted=0" +
            " and b.status=1" +
            " <if test='beginDate!=null and beginDate !=\"\" '>" +
            " and a.publish_time &gt;= #{beginDate} " +
            " </if>" +
            " <if test='endDate!=null and endDate !=\"\" '>" +
            " and a.publish_time &lt;= #{endDate} " +
            " </if>" +
            " <if test='columnId!=null and columnId !=\"\" '>" +
            " and b.column_id = #{columnId} " +
            " </if>" +
            " <if test='title!=null and title !=\"\" '>" +
            " and a.content_title like  CONCAT('%',#{title},'%')  " +
            " </if>" +
            " <if test='createdBy!=null and createdBy !=\"\" '>" +
            " and a.created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and a.id = #{id} " +
            " </if>" +
            " <if test='status!=null and status !=\"\" '>" +
            " and a.status = #{status} " +
            " </if>" +
            " order by b.gmt_updated desc" +
            " limit #{current},#{size}" +
            "</script>"
    })
    List<WebContentVo> quoteList(@Param("id") Integer id, @Param("createdBy") String createdBy, @Param("beginDate") String beginDate, @Param("endDate") String endDate, @Param("title") String title, @Param("status") Integer status, @Param("columnId") Integer columnId, @Param("current") Integer current, @Param("size") Integer size);

    @Select({"<script>" +
            "SELECT count(*) from web_content a,web_quote_content b" +
            " where a.id=b.content_id" +
            " and a.is_deleted=0" +
            " <if test='beginDate!=null and beginDate !=\"\" '>" +
            " and a.publish_time &gt;= #{beginDate} " +
            " </if>" +
            " <if test='endDate!=null and endDate !=\"\" '>" +
            " and a.publish_time &lt;= #{endDate} " +
            " </if>" +
            " <if test='columnId!=null and columnId !=\"\" '>" +
            " and b.column_id = #{columnId} " +
            " </if>" +
            " <if test='title!=null and title !=\"\" '>" +
            " and a.content_title like CONCAT('%',#{title},'%')  " +
            " </if>" +
            " <if test='createdBy!=null and createdBy !=\"\" '>" +
            " and a.created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and a.id = #{id} " +
            " </if>" +
            " <if test='status!=null and status !=\"\" '>" +
            " and a.status = #{status} " +
            " </if>" +
            "</script>"
    })
    Integer quoteListCount(@Param("id") Integer id, @Param("createdBy") String createdBy, @Param("beginDate") String beginDate, @Param("endDate") String endDate, @Param("title") String title, @Param("status") Integer status, @Param("columnId") Integer columnId, @Param("current") Integer current, @Param("size") Integer size);

    @Select("SELECT count(*) from web_content where website_id = #{wensiteId} and status = 1 and is_deleted = 0")
    Integer getPublishCount(Integer websiteId);

    @Select({"<script>" +
            "SELECT distinct a.* from web_content a" +
            " <if test='mediaTagId!=null and mediaTagId !=\"\" '>" +
            " ,web_content_ref_mediatag b" +
            " </if>" +
            " where a.column_id= #{columnId} " +
            " <if test='mediaTagId!=null and mediaTagId !=\"\" '>" +
            " and media_tag_id = #{mediaTagId} and a.id=b.content_id " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and center_id = #{centerId}" +
            " </if>" +
            " and a.is_deleted=0" +
            " and a.status=1" +
            " order by a.gmt_updated desc" +
            " limit #{current},#{size}" +
            "</script>"
    })
    List<WebContentPublishVo> contentListByTagId(@Param("mediaTagId") Integer mediaTagId, @Param("columnId") Integer columnId, @Param("centerId") Integer centerId, @Param("current") Integer curent, @Param("size") Integer size);

    @Select({"<script>" +
            "SELECT distinct a.* from web_content a" +
            " <if test='mediaTagId!=null and mediaTagId !=\"\" '>" +
            " ,web_content_ref_mediatag b" +
            " </if>" +
            " where a.column_id= #{columnId} " +
            " <if test='mediaTagId!=null and mediaTagId !=\"\" '>" +
            " and media_tag_id = #{mediaTagId} and a.id=b.content_id " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and center_id = #{centerId}" +
            " </if>" +
            " and a.is_deleted=0" +
            " and a.status=1" +
            " order by a.gmt_updated desc" +
            "</script>"
    })
    List<WebContentPublishVo> contentTotalByTagId(@Param("mediaTagId") Integer mediaTagId, @Param("columnId") Integer columnId, @Param("centerId") Integer centerId);

    @Select("select * from web_content_ref_mediatag where content_id = #{contentId} and media_tag_id = #{tag}")
    JSONObject checkTag(Integer contentId, Integer tag);

    @Select({"<script>" +
            "SELECT * from system_media_tag where is_deleted = 0 and id in(select media_tag_id from web_content_ref_mediatag where content_id in (      SELECT id FROM `web_content` where column_id = #{columnId}" +
            " and status = 1 " +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and center_id = #{centerId}" +
            " </if>" +
            "))" +
            "</script>"
    })
    List<MediaTag> queryTagByColumnId(String columnId, String centerId);

    @Select({"<script>" +
            "SELECT DISTINCT a.type,b.content_id as id,a.url,a.cover_url,d.gmt_updated\n" +
            "from system_file a,system_file_ref_content b,web_content d where d.id = b.content_id and d.status = 1 and d.is_deleted = 0 and a.id in(select file_id from system_file_ref_content c) and a.id = b.file_id and b.content_id in (select content_id from web_quote_content where column_id = #{columnId})" +
            " <if test='type !=null and type !=\"\" '>" +
            " and a.type = #{type}" +
            " </if>" +
            "ORDER BY d.gmt_updated desc " +
            "</script>"
    })
    List<LinkedHashMap<String, String>> queryPicUrlById(String columnId, String type);

    @Select("select id from web_content order by id desc limit 0,1")
    Integer queryMaxCount();


    @Select({"<script>" +
            "SELECT a.* from web_content a" +
            " where a.is_deleted=0" +
            " <if test='beginDate!=null and beginDate !=\"\" '>" +
            " and a.publish_time &gt;= #{beginDate} " +
            " </if>" +
            " <if test='endDate!=null and endDate !=\"\" '>" +
            " and a.publish_time &lt;= #{endDate} " +
            " </if>" +
            " <if test='columnId!=null and columnId !=\"\" '>" +
            " and a.column_id = #{columnId} " +
            " </if>" +
            " <if test='title!=null and title !=\"\" '>" +
            " and a.content_title like  CONCAT('%',#{title},'%')  " +
            " </if>" +
            " <if test='createdBy!=null and createdBy !=\"\" '>" +
            " and a.created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and a.id = #{id} " +
            " </if>" +
            " <if test='status!=null'>" +
            " and a.status = #{status} " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and a.center_id = #{centerId} " +
            " </if>" +
            " <if test='userName!=null and userName !=\"\" '>" +
            " and a.created_by = #{userName} " +
            " </if>" +
            " <if test='tag!=null and tag !=\"\" '>" +
            " and a.id in (SELECT content_id from web_content_ref_mediatag where media_tag_id = #{tag}) " +
            " </if>" +
            " order by a.top_time desc ,a.publish_time desc" +
            " limit #{current},#{size}" +
            "</script>"
    })
    List<WebContentVo> contentList(@Param("id") Integer id, @Param("createdBy") String createdBy, @Param("beginDate") String beginDate, @Param("endDate") String endDate, @Param("title") String title, @Param("status") Integer status, @Param("columnId") Integer columnId, @Param("current") Integer current, @Param("size") Integer size, @Param("centerId") Integer centerId, @Param("tag") Integer tag, @Param("userName") String userName);


    @Select({"<script>" +
            "SELECT count(*) from web_content a " +
            " where a.is_deleted=0" +
            " <if test='beginDate!=null and beginDate !=\"\" '>" +
            " and a.publish_time &gt;= #{beginDate} " +
            " </if>" +
            " <if test='endDate!=null and endDate !=\"\" '>" +
            " and a.publish_time &lt;= #{endDate} " +
            " </if>" +
            " <if test='columnId!=null and columnId !=\"\" '>" +
            " and a.column_id = #{columnId} " +
            " </if>" +
            " <if test='title!=null and title !=\"\" '>" +
            " and a.content_title like  CONCAT('%',#{title},'%')  " +
            " </if>" +
            " <if test='createdBy!=null and createdBy !=\"\" '>" +
            " and a.created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and a.id = #{id} " +
            " </if>" +
            " <if test='status!=null and status !=\"\" '>" +
            " and a.status = #{status} " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and a.center_id = #{centerId} " +
            " </if>" +
            " <if test='userName!=null and userName !=\"\" '>" +
            " and a.created_by = #{userName} " +
            " </if>" +
            " <if test='tag!=null and tag !=\"\" '>" +
            " and a.id in (SELECT content_id from web_content_ref_mediatag where media_tag_id = #{tag}) " +
            " </if>" +
            " order by a.gmt_updated desc" +
            "</script>"
    })
    Integer contentCount(@Param("id") Integer id, @Param("createdBy") String createdBy, @Param("beginDate") String beginDate, @Param("endDate") String endDate, @Param("title") String title, @Param("status") Integer status, @Param("columnId") Integer columnId, @Param("centerId") Integer centerId, @Param("tag") Integer tag, @Param("userName") String userName);

    @Select({"<script>" +
            "SELECT a.* from web_content a,web_quote_content b" +
            " where a.id=b.content_id " +
            " and a.is_deleted=0" +
            " and b.column_id=60" +
            " and a.status=1" +
            " order by a.publish_time desc" +
            " limit 0,7" +
            "</script>"
    })
    List<WebContent> queryTalent();
}
